package com.ws.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.ws.shop.bean.Page;
import com.ws.shop.dao.LetterDao;
import com.ws.shop.entity.Letter;
import com.ws.shop.util.JdbcUtil;

@Repository(value="letterDao")
public class LetterDaoImpl extends BaseDaoImpl implements LetterDao {

	final String findLetterListPage_count = "select count(1) from jvf_letter";
	final String findLetterListPage_list = "select id,sendname,receptionname,message,type,addtime,state from jvf_letter";
	final String findLetterListPage_order = " order by id desc";
	public Page findLetterListPage(long pageno,int pagesize) {
		return findPage(pageno,pagesize,findLetterListPage_count
				,new StringBuilder(findLetterListPage_list).append(findLetterListPage_order));
	}
	
	final String query_user_name = "select name from jvf_user";
	final String sendAllLetter = "insert into jvf_letter(sendname,receptionname,message,type,addtime,state) values(?,?,?,?,now(),?)";
	@SuppressWarnings("unchecked")
	public void sendAllLetter(final Letter letter) {
		this.execute(new ConnectionCallback(){
			public Object doInConnection(Connection conn) throws SQLException,
					DataAccessException {
				PreparedStatement ps = null;
				ResultSet rs = null;
				Statement stmt = null;
				try{
					stmt = conn.createStatement();
					rs = stmt.executeQuery(query_user_name);
					ps = conn.prepareStatement(sendAllLetter);
					int i = 0;
					while(rs.next()){
						letter.setReceptionname(rs.getString(1));
						ps.setString(1, letter.getSendname());
						ps.setString(2, letter.getReceptionname());
						ps.setString(3, letter.getMessage());
						ps.setInt(4, letter.getType());
						ps.setInt(5, letter.getState());
						ps.addBatch();
						i++;
						if(i >= 1000){
							ps.executeBatch();
							i = 0;
						}
					}
					if( i > 0){
						ps.executeBatch();
					}
				}finally{
					JdbcUtil.closeStatement(stmt);
					JdbcUtil.close(rs, ps, null);
				}
				return null;
			}
		});
	}

	final String sendLetter = "insert into jvf_letter(sendname,receptionname,message,type,addtime,state) values(?,?,?,?,now(),?)";
	public void sendLetter(Letter letter) {
		this.update(sendLetter, letter.getSendname(),letter.getReceptionname(),letter.getMessage(),letter.getType(),letter.getState());
	}
	
	public Page getLetterByUserId(long pageno, int pagesize,String username){
		String  count_sql = "select count(1) from jvf_letter where  receptionname = '"+username+"'";
		StringBuilder list_sql = new StringBuilder("select id,sendname,receptionname,message,type,addtime,state from jvf_letter where  receptionname = '"+username+"'");
		return this.findPage(pageno, pagesize, count_sql, list_sql);
	}
	


	//查找指定的
	public List<Letter> getLetters(int type,String username){
		String s = "";
		  if(type==0 || type ==1){
			 s = "select id,sendname,receptionname,message,type,addtime,state from jvf_letter where type = "+type+" and receptionname = '"+username+"'" ;
		  }else{
			 s = "select id,sendname,receptionname,message,type,addtime,state from jvf_letter where type = 1 and sendname = '" + username +"'";
		  }
		  return this.query(s, new ResultSetExtractor<List<Letter>>() {
				public List<Letter> extractData(ResultSet rs) throws SQLException,
						DataAccessException {
					List<Letter> list = new LinkedList<Letter>();
					Letter letter = null;
					while (rs.next()) {
						letter = new Letter();
						letter.setId(rs.getInt(1));
                        letter.setSendname(rs.getString(2));
                        letter.setReceptionname(rs.getString(3));
                        letter.setMessage(rs.getString(4));
                        letter.setType(rs.getInt(5));
                        letter.setAddtime(rs.getTimestamp(6));
                        letter.setState(rs.getInt(7));
						list.add(letter);
					}
					return list;
				}
		  });
	}
	
	public int updateState(int type,String username){
		try{
			 String s = "";
			 if(type==0 || type ==1){
				 s = "update jvf_letter set state = 1 where type = "+type+" and receptionname = "+username;
			 }else{
				 s = "update jvf_letter set state = 1 where type = 1 and sendname = "+username;
			 }
			 return this.update(s);
		}catch(Exception ex){
			ex.printStackTrace();
			return  -1; 
		}
	}
	
	public int delLetter(int type,long id,String username){
		String s = "DELETE FROM jvf_letter WHERE id = " + type + " AND (sendname= '"+username+"' OR  receptionname = '"+username+"')";
		try{
           return this.update(s);
		}catch(Exception ex){
			ex.printStackTrace();
			return  -1; 
		}
	}
	
	public int sendMessage(Letter letter){
		try{
			return this.update(sendLetter, letter.getSendname(),letter.getReceptionname(),letter.getMessage(),letter.getType(),letter.getState());
		}catch(Exception ex){
			ex.printStackTrace();
			return  -1; 
		}
		
	}
	
	public int getLetterCount(String name,int state ){
		String s = "SELECT COUNT(1) FROM  jvf_letter WHERE  receptionname = ? AND state = ? ";
		return this.queryForInt(s, new Object[]{name,state});
	}
	
	
}
